/**
 * $version:  0.1 
 * $Date: 2011-01-21 
 *
 * Copyright (C) 2010-2011 Jawa Software. All rights reserved.
 *
 */

package org.jawa.exam;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.jawa.core.database.DbConnectionManager;
import org.jawa.core.exception.UserNotFoundException;
import org.jawa.exam.model.Exam;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * 
 * 类文件: DefaultExamProvider
 * <p>
 * <p>
 * 类描述：考试信息的默认数据库实现.
 * <p>
 * 作     者： wangyg
 * <p>
 * 日     期： 2012-8-1
 * <p>
 * 时     间： 下午1:46:10
 * <p>
 */
public class DefaultExamProvider implements ExamProvider {

	private static final Logger Log = LoggerFactory.getLogger(DefaultExamProvider.class);

	private static final String LOAD_EXAM_BY_ID = "SELECT * FROM EXAM WHERE exam_id=? ";
	private static final String EXAM_COUNT = "SELECT count(*) FROM EXAM";
	private static final String ALL_EXAMS = "SELECT * FROM EXAM";
	private static final String INSERT_EXAM = "INSERT INTO EXAM(exam_id,paper_id,exam_name,exam_desc,start_time,end_time," +
			"invalid_time,exam_mode,join_num,score_type,convert_score,pass_score,need_manual,public_result,public_time,exam_status," +
			"random_question,random_option,remark) "
			+ " VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
	private static final String DELETE_EXAM = "DELETE FROM EXAM WHERE exam_id=? ";

	/**
	 * 创建考试信息.
	 * 
	 * @param Exam 考试信息.
	 * @return 创建好的考试信息实例.
	 * @throws Exception 创建考试信息异常.
	 */
	public Exam createExam(Exam exam) throws Exception {
		Connection con = null;
		PreparedStatement pstmt = null;
		try {
			con = DbConnectionManager.getConnection();
			pstmt = con.prepareStatement(INSERT_EXAM);
			pstmt.setLong(1, exam.getExamId());
			pstmt.setLong(2, exam.getPaperId());

			if (StringUtils.isEmpty(exam.getExamName())) {
				pstmt.setNull(3, Types.VARCHAR);
			} else {
				pstmt.setString(3, exam.getExamName());
			}
			if(StringUtils.isBlank(exam.getExamDesc())){
				pstmt.setNull(4, Types.VARCHAR);
			}else{
				pstmt.setString(4, exam.getExamDesc());
			}
			
			if(StringUtils.isEmpty(exam.getStartTimeString())){
				pstmt.setNull(5, Types.DATE);
			}else{
				pstmt.setString(5, exam.getStartTimeString());
			}
			
			if (StringUtils.isEmpty(exam.getEndTimeString())) {
				pstmt.setNull(6, Types.DATE);
			} else {
				pstmt.setString(6, exam.getEndTimeString());
			}
			
			if (exam.getInvalidTime()==null) {
				pstmt.setNull(7, Types.BIGINT);
			} else {
				pstmt.setLong(7, exam.getInvalidTime());
			}
			
			if (StringUtils.isEmpty(exam.getExamMode())) {
				pstmt.setNull(8, Types.VARCHAR);
			} else {
				pstmt.setString(8, exam.getExamMode());
			}
			
			if (exam.getJoinNum()==null) {
				pstmt.setNull(9, Types.BIGINT);
			} else {
				pstmt.setLong(9, exam.getJoinNum());
			}
			
			if (StringUtils.isEmpty(exam.getScoreType())) {
				pstmt.setNull(10, Types.VARCHAR);
			} else {
				pstmt.setString(10, exam.getScoreType());
			}
			
			pstmt.setDouble(11, exam.getConvertScore());
			pstmt.setDouble(12, exam.getPassScore());
			
			if (StringUtils.isEmpty(exam.getNeedManual())) {
				pstmt.setNull(13, Types.VARCHAR);
			} else {
				pstmt.setString(13, exam.getNeedManual());
			}
			if (StringUtils.isEmpty(exam.getPublicResult())) {
				pstmt.setNull(14, Types.VARCHAR);
			} else {
				pstmt.setString(14, exam.getPublicResult());
			}
			
			if (StringUtils.isEmpty(exam.getPublicTimeString())) {
				pstmt.setNull(15, Types.VARCHAR);
			} else {
				pstmt.setString(15, exam.getPublicTimeString());
			}
			if (StringUtils.isEmpty(exam.getExamStatus())) {
				pstmt.setNull(16, Types.VARCHAR);
			} else {
				pstmt.setString(16, exam.getExamStatus());
			}
			
			if (StringUtils.isEmpty(exam.getRandomQuestion())) {
				pstmt.setNull(17, Types.VARCHAR);
			} else {
				pstmt.setString(17, exam.getRandomQuestion());
			}
			
			if (StringUtils.isEmpty(exam.getRandomOption())) {
				pstmt.setNull(18, Types.VARCHAR);
			} else {
				pstmt.setString(18, exam.getRandomOption());
			}
			
			if (StringUtils.isEmpty(exam.getRemark())) {
				pstmt.setNull(19, Types.VARCHAR);
			} else {
				pstmt.setString(19, exam.getRemark());
			}

			pstmt.execute();
		} catch (Exception e) {
			Log.error("创建考试信息时出错！", e);
			throw e;
		} finally {
			DbConnectionManager.closeConnection(pstmt, con);
		}
		return exam;
	}
	
	/**
	 * 删除考试信息。
	 * @param ExamID 要删除考试信息的 ExamID.
	 */
	public void deleteExam(Long examID) {
		Connection con = null;
		PreparedStatement pstmt = null;
		try {
			con = DbConnectionManager.getConnection();
			pstmt = con.prepareStatement(DELETE_EXAM);
			pstmt.setLong(1, examID);
			pstmt.execute();
		} catch (Exception e) {
			Log.error("删除考试信息时出错!", e);
		} finally {
			DbConnectionManager.closeConnection(pstmt, con);
		}
	}

	/**
	 * 返回总的考试信息数.
	 * @return 总的考试信息数.
	 */
	public int getExamCount() {
		int count = 0;
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			con = DbConnectionManager.getConnection();
			pstmt = con.prepareStatement(EXAM_COUNT);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				count = rs.getInt(1);
			}
		} catch (SQLException e) {
			Log.error("获取考试信息总数出错！", e);
		} finally {
			DbConnectionManager.closeConnection(rs, pstmt, con);
		}
		return count;
	}

	public Collection<Long> getExamIds() {
		return getExamIds(0, Integer.MAX_VALUE);
	}

	public Collection<Exam> getExams() {
		return null;
	}

	public Collection<Exam> getExams(int startIndex, int numResults) {
		return null;
	}

	public Collection<Exam> findExams(List<String> fields, List<String> querys) {
		return null;
	}
	
	private Collection<Long> getExamIds(int startIndex, int numResults) {
		List<Long> userIds = new ArrayList<Long>(500);
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			con = DbConnectionManager.getConnection();
			if ((startIndex == 0) && (numResults == Integer.MAX_VALUE)) {
				pstmt = con.prepareStatement(ALL_EXAMS);
				DbConnectionManager.setFetchSize(pstmt, 500);
				rs = pstmt.executeQuery();
				while (rs.next()) {
					userIds.add(rs.getLong(1));
				}
			} else {
				pstmt = DbConnectionManager.createScrollablePreparedStatement(con, ALL_EXAMS);
				DbConnectionManager.limitRowsAndFetchSize(pstmt, startIndex, numResults);
				rs = pstmt.executeQuery();
				DbConnectionManager.scrollResultSet(rs, startIndex);
				int count = 0;
				while (rs.next() && count < numResults) {
					userIds.add(rs.getLong(1));
					count++;
				}
			}
			if (Log.isDebugEnabled()) {
				Log.debug("查询结果: " + userIds.size());
				LogResults(userIds);
			}
		} catch (SQLException e) {
			Log.error(e.getMessage(), e);
		} finally {
			DbConnectionManager.closeConnection(rs, pstmt, con);
		}
		return userIds;
	}
	
	/**
	 * 记录详细结果。
	 */
	private void LogResults(List<Long> listElements) {
		String callingMethod = Thread.currentThread().getStackTrace()[3].getMethodName();
		StringBuilder sb = new StringBuilder(256);
		int count = 0;
		for (Long element : listElements) {
			if (count > 20) {
				Log.debug(callingMethod + " 结果: " + sb.toString());
				sb.delete(0, sb.length());
				count = 0;
			}
			sb.append(element).append(",");
			count++;
		}
		sb.append("。");
		Log.debug(callingMethod + " 结果: " + sb.toString());
	}

	/**
	 * 根据考试信息编号获取对象
	 * @param examId 考试信息编号
	 * @return 返回获取的对象实例
	 */
	public Exam loadByExamId(Long examId){
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Exam exam = null;
		try {
			con = DbConnectionManager.getConnection();
			pstmt = con.prepareStatement(LOAD_EXAM_BY_ID);
			pstmt.setLong(1, examId);
			rs = pstmt.executeQuery();
			if (!rs.next()) {
				throw new UserNotFoundException();
			}
			examId = rs.getLong("exam_id");
			Long paperId = rs.getLong("paper_id");
			String examName = rs.getString("exam_name");
			String examDesc = rs.getString("exam_desc");
			Date startTime = rs.getDate("start_time");
			Date endTime=rs.getDate("end_time");
			
			Long invalidTime=rs.getLong("invalid_time");
			
			String examMode = rs.getString("exam_mode");
			Long joinNum=rs.getLong("join_num");
			
			String scoreType = rs.getString("score_type");
			double convertScore=rs.getDouble("convert_score");
			double passScore=rs.getDouble("pass_score");
			
			String needManual = rs.getString("need_manual");
			String publicResult = rs.getString("public_result");
			Date publicTime=rs.getDate("public_time");
			
			String examStatus = rs.getString("exam_status");
			String randomQuestion = rs.getString("random_question");
			String randomOption = rs.getString("random_option");
			String remark = rs.getString("remark");

			exam=new Exam();
			exam.setExamId(examId);
			exam.setPaperId(paperId);
			exam.setExamName(examName);
			exam.setExamDesc(examDesc);
			exam.setStartTime(startTime);
			exam.setEndTime(endTime);
			exam.setInvalidTime(invalidTime);
			exam.setExamMode(examMode);
			exam.setJoinNum(joinNum);
			exam.setScoreType(scoreType);
			exam.setConvertScore(convertScore);
			exam.setPassScore(passScore);
			exam.setNeedManual(needManual);
			exam.setPublicResult(publicResult);
			exam.setPublicTime(publicTime);
			exam.setExamStatus(examStatus);
			exam.setRandomQuestion(randomQuestion);
			exam.setRandomOption(randomOption);
			exam.setRemark(remark);
			return exam;
		} catch (Exception e) {
			Log.error("获取编号为{0}考试信息时出错！", examId);
			return exam;
		} finally {
			DbConnectionManager.closeConnection(rs, pstmt, con);
		}
	}

}
